package org.example.repository;

import org.example.entity.ServiceRequest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 服务申请数据访问层
 */
@Repository
public interface ServiceRequestRepository extends JpaRepository<ServiceRequest, Long> {
    
    /**
     * 根据用户ID查找服务申请
     */
    List<ServiceRequest> findByUserIdOrderByCreatedAtDesc(Long userId);
    
    /**
     * 根据用户ID分页查找服务申请
     */
    Page<ServiceRequest> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);
    
    /**
     * 根据状态查找服务申请
     */
    List<ServiceRequest> findByStatusOrderByCreatedAtDesc(String status);
    
    /**
     * 根据状态分页查找服务申请
     */
    Page<ServiceRequest> findByStatusOrderByCreatedAtDesc(String status, Pageable pageable);
    
    /**
     * 根据紧急程度查找服务申请
     */
    List<ServiceRequest> findByUrgencyLevelOrderByCreatedAtDesc(String urgencyLevel);
    
    /**
     * 根据紧急程度分页查找服务申请
     */
    Page<ServiceRequest> findByUrgencyLevelOrderByCreatedAtDesc(String urgencyLevel, Pageable pageable);
    
    /**
     * 根据用户ID和状态查找服务申请
     */
    List<ServiceRequest> findByUserIdAndStatusOrderByCreatedAtDesc(Long userId, String status);
    
    /**
     * 根据用户ID和状态分页查找服务申请
     */
    Page<ServiceRequest> findByUserIdAndStatusOrderByCreatedAtDesc(Long userId, String status, Pageable pageable);
    
    /**
     * 统计用户的服务申请数量
     */
    long countByUserId(Long userId);
    
    /**
     * 统计指定状态的服务申请数量
     */
    long countByStatus(String status);
    
    /**
     * 统计指定紧急程度的服务申请数量
     */
    long countByUrgencyLevel(String urgencyLevel);
    
    /**
     * 查找指定时间范围内的服务申请
     */
    @Query("SELECT sr FROM ServiceRequest sr WHERE sr.appointmentTime BETWEEN :startTime AND :endTime ORDER BY sr.appointmentTime ASC")
    List<ServiceRequest> findByAppointmentTimeBetween(@Param("startTime") LocalDateTime startTime, 
                                                      @Param("endTime") LocalDateTime endTime);
    
    /**
     * 查找需要处理的服务申请（待处理和紧急的）
     */
    @Query("SELECT sr FROM ServiceRequest sr WHERE sr.status = 'PENDING' AND sr.urgencyLevel IN ('URGENT', 'VERY_URGENT') ORDER BY sr.urgencyLevel DESC, sr.createdAt ASC")
    List<ServiceRequest> findUrgentPendingRequests();
    
    /**
     * 统计用户各状态的服务申请数量
     */
    @Query("SELECT sr.status, COUNT(sr) FROM ServiceRequest sr WHERE sr.userId = :userId GROUP BY sr.status")
    List<Object[]> countByUserIdAndStatus(@Param("userId") Long userId);
}
